package cn.buk.tms.report.service;

import cn.buk.qms.BillUtil;
import cn.buk.tms.dto.ReportTicketDto;
import cn.buk.tms.dto.SettlementReportDto;
import cn.buk.tms.dto.SettlementReportVasDto;
import cn.buk.tms.entity.bill.BillHotelBooking;
import cn.buk.tms.entity.bill.BillRentalCarOrder;
import cn.buk.tms.entity.bill.BillTrainTicket;
import cn.buk.common.util.DateUtil;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import static cn.buk.common.Constant.DATE_YYYY_MM_DD;

public class ReportExportExcelFormat2 extends BaseReportExportExcel {

  public static double exportSettlementToExcel(SettlementReportDto dto, Sheet sheet) {
    //输出机票明细
    int curDataRow = -1;

    int flightSubTotalRow = -1;
    if (!dto.getTickets().isEmpty()) {
      curDataRow = exportSettlementFlight(dto, sheet, curDataRow + 1);
      flightSubTotalRow = curDataRow;
    }

    //输出酒店明细
    int hotelSubTotalRow = -1;
    if (!dto.getHotels().isEmpty()) {
      curDataRow = exportSettlementHotel(dto, sheet, curDataRow + 1);
      hotelSubTotalRow = curDataRow;
    }

    //输出火车票明细
    int trainSubTotalRow = -1;
    if (!dto.getTrains().isEmpty()) {
      curDataRow = exportSettlementTrain(dto, sheet, curDataRow + 1);
      trainSubTotalRow = curDataRow;
    }


    //输出服务明细
    int vasSubTotalRow = -1;
    if (!dto.getVasOrders().isEmpty()) {
      curDataRow = exportSettlementVas(dto, sheet, curDataRow + 1);
      vasSubTotalRow = curDataRow;
    }

    //输出用车
    int carSubTotalRow = -1;
    if (!dto.getCarOrders().isEmpty()) {
      curDataRow = exportSettlementCar(dto, sheet, curDataRow + 1);
      carSubTotalRow = curDataRow;
    }

    //汇总
     return exportSettlementSummary(flightSubTotalRow+1,
              hotelSubTotalRow+1,
              trainSubTotalRow + 1,
              vasSubTotalRow + 1,
             carSubTotalRow + 1,
              sheet, curDataRow + 2);
  }

  private static int exportSettlementFlight(final SettlementReportDto dto, final Sheet sheet, final int startRowNo) {

    //标题行
    int currentRowNo = startRowNo;
    createTitleRow(dto.getName() + " - 机票明细", sheet, currentRowNo, 0, 12);

    //表头行
    currentRowNo++;
    createTableHeaderRow(sheet, currentRowNo, "订单日期", "乘机人", "出发",
            "到达", "航班号", "起飞日期",
            "起飞时间", "票面价", "税收",
            "应付", "票号", "备注");
    setColumnWidth(sheet, 15, 20, 20,
            10, 10, 15,
            15, 15, 20,
            15, 15, 30);


    //表内容
    Row row = null;
    int col = -1;
    int firstRow = -1;
    int lastRow = -1;

    for(ReportTicketDto t: dto.getTickets()) {
      currentRowNo++;

      if (firstRow == -1) {
        firstRow = (currentRowNo + 1); //用于单元格表达式，表达式的行号从一开始
      }
      lastRow = currentRowNo;

      int idx = 0;
      for(var detailDto: t.getDetails()) {
        if (idx > 0) {
          currentRowNo++;
        }
        exportFlight(t, sheet, currentRowNo, idx);
        idx++;
      }
    }

    for(ReportTicketDto t: dto.getRefunds()) {
      currentRowNo++;

      if (firstRow == -1) {
        firstRow = (currentRowNo + 1); //用于单元格表达式，表达式的行号从一开始
      }
      lastRow = currentRowNo;

      int idx = 0;
      for(var detailDto: t.getDetails()) {
        if (idx > 0) {
          currentRowNo++;
        }
        exportFlight(t, sheet, currentRowNo, idx);
        idx++;
      }
    }

    for(ReportTicketDto t: dto.getChanges()) {
      currentRowNo++;

      if (firstRow == -1) {
        firstRow = (currentRowNo + 1); //用于单元格表达式，表达式的行号从一开始
      }
      lastRow = currentRowNo;

      int idx = 0;
      for(var detailDto: t.getDetails()) {
        if (idx > 0) {
          currentRowNo++;
        }
        exportFlight(t, sheet, currentRowNo, idx);
        idx++;
      }
    }

    //小计
    currentRowNo++;
    lastRow = lastRow + 1; //用于单元格表达式，表达式中的行号从1开始
    row = getRow(sheet, currentRowNo);
    col = -1;
    col++; row.createCell(col).setCellValue("小计");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellFormula("sum(J" + firstRow + ":J" + lastRow + ")");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");

    return currentRowNo;
  }

  private static void exportFlight(ReportTicketDto t, Sheet sheet, int curDataRow, final int flightIdx) {
    Row row = getRow(sheet, curDataRow);
    int col = -1;
    col++; row.createCell(col).setCellValue(DateUtil.formatDate(t.getOrderDate(), DATE_YYYY_MM_DD));
    col++; row.createCell(col).setCellValue(t.getPassengerName());
    col++; row.createCell(col, CellType.STRING).setCellValue(t.getDetails().get(flightIdx).getDportName());
    col++; row.createCell(col).setCellValue(t.getDetails().get(flightIdx).getAportName());
    col++; row.createCell(col).setCellValue(t.getDetails().get(flightIdx).getFlight());
    col++; row.createCell(col).setCellValue(t.getDetails().get(flightIdx).getDdate());
    col++; row.createCell(col).setCellValue(t.getDetails().get(flightIdx).getDtime());
    if (flightIdx == 0) {
      col++; row.createCell(col).setCellValue(t.getParvalue() + t.getServiceCharge() - t.getDiscount());
      col++; row.createCell(col).setCellValue(t.getTax());
      col++; row.createCell(col).setCellValue(t.getAmount());
    } else {
      col++;
      row.createCell(col);
      col++;
      row.createCell(col);
      col++;
      row.createCell(col);
    }

    col++; row.createCell(col).setCellValue(t.getShowTicketNo());

    String temp = "机票";
    if (t.getTicketType() == 1) {
      temp = "退票";
    } else if (t.getTicketType() == 2) {
      temp = "改签";
    }
    col++;
    row.createCell(col).setCellValue(temp);

  }


  private static int exportSettlementHotel(final SettlementReportDto dto, final Sheet sheet, final int startRowNo) {
    final String customerName = dto.getCustomer().getName();

    //标题行
    int currentRowNo = startRowNo;
    createTitleRow(dto.getName() + " - 酒店明细", sheet, currentRowNo, 0, 12);

    //表头行
    currentRowNo++;
    createTableHeaderRow(sheet, currentRowNo, "序列", "部门", "酒店",
            "房型", "房间数", "天数",
            "入住日期", "离店日期", "入住人",
            "垫付金额", "服务费", "用车费", "应收款", "支付方式", "订单日期", "备注");

    Row row = null;
    int col = -1;
    int sn = 0;
    int firstRow = -1;
    int lastRow = -1;

    for(BillHotelBooking t: dto.getHotels()) {
      currentRowNo++;
      sn++;

      if (firstRow == -1) {
        firstRow = currentRowNo + 1;
      }
      lastRow = currentRowNo;

      row = getRow(sheet, currentRowNo);
      col = -1;
      col++; row.createCell(col).setCellValue(sn + "");
      col++; row.createCell(col).setCellValue(customerName);
      col++; row.createCell(col, CellType.STRING).setCellValue(t.getHotelName());
      col++; row.createCell(col).setCellValue(t.getRoomType());
      col++; row.createCell(col).setCellValue(t.getRoomCount());
      col++; row.createCell(col).setCellValue(t.getCount());
      col++; row.createCell(col).setCellValue(DateUtil.formatDate(t.getDdate(), DATE_YYYY_MM_DD));
      col++; row.createCell(col).setCellValue(DateUtil.formatDate(t.getLeaveDate(), DATE_YYYY_MM_DD));
      col++; row.createCell(col).setCellValue(t.getPassengerName());
      col++; row.createCell(col).setCellValue(t.getPrice());
      col++; row.createCell(col).setCellValue(t.getServiceCharge());
      col++; row.createCell(col).setCellValue("");
      col++; row.createCell(col).setCellValue(t.getTotalAmount());
      col++; row.createCell(col).setCellValue("");
      col++; row.createCell(col).setCellValue(DateUtil.formatDate(t.getSaleDate(), DATE_YYYY_MM_DD));
      col++; row.createCell(col).setCellValue("");
    }

    //小计
    currentRowNo++;
    lastRow = lastRow + 1;
    row = getRow(sheet, currentRowNo);
    col = -1;
    col++; row.createCell(col).setCellValue("小计");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellFormula(String.format("sum(J%d:J%d)", firstRow, lastRow));
    col++; row.createCell(col).setCellFormula(String.format("sum(K%d:K%d)", firstRow, lastRow));
    col++; row.createCell(col);
    col++; row.createCell(col).setCellFormula(String.format("sum(M%d:M%d)", firstRow, lastRow));
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");

    return currentRowNo;
  }

  private static int exportSettlementTrain(final SettlementReportDto dto, final Sheet sheet, final int startRowNo) {
    final String customerName = dto.getCustomer().getName();

    //标题行
    int currentRowNo = startRowNo;
    createTitleRow(dto.getName() + " - 火车票明细", sheet, currentRowNo, 0, 12);

    //表头行
    currentRowNo++;
    createTableHeaderRow(sheet, currentRowNo, "序列", "部门", "车次", "日期", "出发", "到达",
            "等级", "", "乘车人", "票款", "服务费", "", "应收款",
            "", "订单日期", "备注");

    Row row = null;
    int col = -1;

    int sn = 0;
    int firstRow = -1;
    int lastRow = -1;

    for(BillTrainTicket t: dto.getTrains()) {
      currentRowNo++;
      sn++;

      if (firstRow == -1) {
        firstRow = currentRowNo + 1;
      }
      lastRow = currentRowNo;

      row = getRow(sheet, currentRowNo);
      col = -1;
      col++; row.createCell(col).setCellValue(sn + "");
      col++; row.createCell(col).setCellValue(customerName);
      col++; row.createCell(col, CellType.STRING).setCellValue(t.getTrainNo());
      col++; row.createCell(col).setCellValue(DateUtil.formatDate(t.getDdate(), DATE_YYYY_MM_DD));
      col++; row.createCell(col).setCellValue(t.getDcity());
      col++; row.createCell(col).setCellValue(t.getAcity());
      col++; row.createCell(col).setCellValue(t.getSeatClass());
      col++; row.createCell(col).setCellValue("");
      col++; row.createCell(col).setCellValue(t.getPassengerName());
      col++; row.createCell(col).setCellValue(t.getPrice() * t.getPsgCount());
      col++; row.createCell(col).setCellValue(t.getServiceCharge());
      col++; row.createCell(col).setCellValue("");
      col++; row.createCell(col).setCellValue(t.getTotal());
      col++; row.createCell(col).setCellValue("");
      col++; row.createCell(col).setCellValue(DateUtil.formatDate(t.getSaleDate(), DATE_YYYY_MM_DD));
      col++; row.createCell(col).setCellValue("");
    }

    //小计
    currentRowNo++;
    lastRow = lastRow + 1;
    row = getRow(sheet, currentRowNo);
    col = -1;
    col++; row.createCell(col).setCellValue("小计");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellFormula("sum(J" + firstRow + ":J" + lastRow + ")");
    col++; row.createCell(col).setCellFormula("sum(K" + firstRow + ":K" + lastRow + ")");
    col++; row.createCell(col);
    col++; row.createCell(col).setCellFormula("sum(M" + firstRow + ":M" + lastRow + ")");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");

    return currentRowNo;
  }

  private static int exportSettlementCar(SettlementReportDto dto, final Sheet sheet, final int startRowNo) {
    //标题行
    int currentRowNo = startRowNo;
    createTitleRow(dto.getName() + " - 服务明细", sheet, currentRowNo, 0, 15);

    //表头行
    currentRowNo++;
    createTableHeaderRow(sheet, currentRowNo, "序列", "用车类型", "城市", "出发", "到达",
            "用车时间", "车型", "联系人", "应收款", "备注");

    //表内容
    Row row = null;
    int col = -1;
    int sn = 0;
    int firstRow = -1;
    int lastRow = -1;

    for(BillRentalCarOrder t: dto.getCarOrders()) {
      currentRowNo++;
      sn++;

      if (firstRow == -1) {
        firstRow = currentRowNo + 1;
      }
      lastRow = currentRowNo;
      row = getRow(sheet, currentRowNo);
      col = -1;
      col++; row.createCell(col).setCellValue(sn + "");
      col++; row.createCell(col).setCellValue(BillUtil.getCarUseType(t.getUseType()));
      col++; row.createCell(col, CellType.STRING).setCellValue(t.getCityName());
      col++; row.createCell(col).setCellValue(t.getDepartureAddress());
      col++; row.createCell(col).setCellValue(t.getArrivalAddress());
      col++; row.createCell(col).setCellValue(DateUtil.formatDate(t.getUseTime(), DATE_YYYY_MM_DD));
      col++; row.createCell(col).setCellValue(t.getVehicleTypeName());
      col++; row.createCell(col).setCellValue(t.getRemark());
      col++; row.createCell(col).setCellValue(t.getPrice() - t.getDiscount() + t.getServiceCharge());
      col++; row.createCell(col).setCellValue(t.getRemark());
    }

    //小计
    currentRowNo++;
    lastRow += 1;
    row = getRow(sheet, currentRowNo);
    col = -1;
    col++; row.createCell(col).setCellValue("小计");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellFormula("sum(I" + firstRow + ":I" + lastRow + ")");
    col++; row.createCell(col).setCellValue("");

    return currentRowNo;
  }



  private static int exportSettlementVas(final SettlementReportDto dto, final Sheet sheet, final int startRowNo) {
    //标题行
    int currentRowNo = startRowNo;
    createTitleRow(dto.getName() + " - 服务明细", sheet, currentRowNo, 0, 15);

    //表头行
    currentRowNo++;
    createTableHeaderRow(sheet, currentRowNo, "序列", "编号", "车型", "座位", "去程日期", "返程日期",
            "行程", "行程", "乘车人", "垫付金额", "司机餐补", "用车费", "应收款",
            "支付方式", "订单日期", "备注");
    //表内容
    Row row = null;
    int col = -1;
    int sn = 0;
    int firstRow = -1;
    int lastRow = -1;

    for(SettlementReportVasDto t: dto.getVasOrders()) {
      currentRowNo++;
      sn++;

      if (firstRow == -1) {
        firstRow = currentRowNo + 1;
      }
      lastRow = currentRowNo;
      row = getRow(sheet, currentRowNo);
      col = -1;
      col++; row.createCell(col).setCellValue(sn + "");
      col++; row.createCell(col).setCellValue("");
      col++; row.createCell(col, CellType.STRING).setCellValue(t.getProductName());
      col++; row.createCell(col).setCellValue("");
      col++; row.createCell(col).setCellValue("");
      col++; row.createCell(col).setCellValue("");
      col++; row.createCell(col).setCellValue("");
      col++; row.createCell(col).setCellValue(t.getRemark());
      col++; row.createCell(col).setCellValue("");
      col++; row.createCell(col).setCellValue("");
      col++; row.createCell(col).setCellValue("");
      col++; row.createCell(col).setCellValue(t.getTotal());
      col++; row.createCell(col).setCellValue(t.getTotal());
      col++; row.createCell(col).setCellValue("");
      col++; row.createCell(col).setCellValue(DateUtil.formatDate(t.getOrderDate(), DATE_YYYY_MM_DD));
      col++; row.createCell(col).setCellValue(t.getRemark());
    }

    //小计
    currentRowNo++;
    lastRow += 1;
    row = getRow(sheet, currentRowNo);
    col = -1;
    col++; row.createCell(col).setCellValue("小计");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col);
    col++; row.createCell(col);
    col++; row.createCell(col).setCellFormula("sum(L" + firstRow + ":L" + lastRow + ")");
    col++; row.createCell(col).setCellFormula("sum(M" + firstRow + ":M" + lastRow + ")");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");
    col++; row.createCell(col).setCellValue("");

    return currentRowNo;
  }

  private static double exportSettlementSummary(final int flightSubTotalRow,
                                             final int hotelSubTotalRow,
                                             final int trainSubTotalRow,
                                             final int vasSubTotalRow,
                                      final int carSubTotalRow,
                                      final Sheet sheet, final int startRowNo) {
    int curDataRow = startRowNo;
    Row row = getRow(sheet, curDataRow);
    int col = -1;
    col++; row.createCell(col);
    col++; row.createCell(col);
    col++; row.createCell(col).setCellValue("机票款：");
    col++; row.createCell(col).setCellFormula("J" + flightSubTotalRow);

    if (hotelSubTotalRow > 0) {
      curDataRow++;
      row = getRow(sheet, curDataRow);
      col = -1;
      col++;
      row.createCell(col);
      col++;
      row.createCell(col);
      col++;
      row.createCell(col).setCellValue("酒店费用：");
      col++;
      row.createCell(col).setCellFormula("M" + hotelSubTotalRow);
    }

    if (trainSubTotalRow > 0) {
      curDataRow++;
      row = getRow(sheet, curDataRow);
      col = -1;
      col++;
      row.createCell(col);
      col++;
      row.createCell(col);
      col++;
      row.createCell(col).setCellValue("火车票：");
      col++;
      row.createCell(col).setCellFormula("J" + trainSubTotalRow);
    }
    if (carSubTotalRow > 0) {
      curDataRow++;
      row = getRow(sheet, curDataRow);
      col = -1;
      col++;
      row.createCell(col);
      col++;
      row.createCell(col);
      col++;
      row.createCell(col).setCellValue("用车：");
      col++;
      row.createCell(col).setCellFormula("I" + carSubTotalRow);
    }

    if (vasSubTotalRow > 0) {
      curDataRow++;
      row = getRow(sheet, curDataRow);
      col = -1;
      col++;
      row.createCell(col);
      col++;
      row.createCell(col);
      col++;
      row.createCell(col).setCellValue("其它服务：");
      col++;
      row.createCell(col).setCellFormula("M" + vasSubTotalRow);
    }


    curDataRow++;
    row = getRow(sheet, curDataRow);
    col = -1;
    col++; row.createCell(col);
    col++; row.createCell(col);
    col++; row.createCell(col).setCellValue("总计：");
    col++; row.createCell(col).setCellFormula("sum(D" + (startRowNo+1) + ":D" + curDataRow + ")");

    //试试获取评估的总值
    return evaluateTotal("D" + (curDataRow + 1), sheet);
  }


}
